# -*- coding: utf-8 -*-

# @File    : keyword.py
# @Date    : 2019-09-18
# @Author  : gongsunmolans

import jieba
import pymysql
from gensim import corpora, models, similarities
import os

doc_test = "2004页面阵列不可用"
doc_test = "云存储需不需要硬件加密狗？"

# 添加自定义词典
# file_name 为文件类对象或自定义词典的路径
jieba.load_userdict('E:/03/wechat_robot/Resources/dic/dict.txt')

all_doc = []

# 打开数据库连接
db = pymysql.connect("localhost", "root", "", "wechat")
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL 插入语句
# sql = """SELECT * FROM  knowledge keywords =="""" +  keywords +"/""

sql = """select keywords from knowledge """
print(sql)
try:
    # 执行sql语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:  # 从fetchall中读取操作

        # print(row)
        str1 = str(row)
        # print(str1)
        str2 = str1[2:-3]
        # print(str2)
        all_doc.append(str2)

except:
    # 如果发生错误则回滚
    db.rollback()
# 关闭数据库连接
db.close()

all_doc_list = []
for doc in all_doc:
    doc_list = [word for word in jieba.cut(doc)]
    all_doc_list.append(doc_list)

print(all_doc_list)

doc_test_list = [word for word in jieba.cut(doc_test)]
print(doc_test_list)

dictionary = corpora.Dictionary(all_doc_list)
dictionary.keys()
dictionary.token2id
corpus = [dictionary.doc2bow(doc) for doc in all_doc_list]

doc_test_vec = dictionary.doc2bow(doc_test_list)
doc_test_vec
print(doc_test_vec)
tfidf = models.TfidfModel(corpus)
tfidf[doc_test_vec]

index = similarities.SparseMatrixSimilarity(tfidf[corpus], num_features=len(dictionary.keys()))
sim = index[tfidf[doc_test_vec]]

# print(sim)
a = sorted(enumerate(sim), key=lambda item: -item[1])

print(a[0][0])
print(a[0][1])
# for x in a:
#    print(x)

if a[0][1] > 0.75:
    print("相似度满足条件")
    print(all_doc[a[0][0]])
    keywords = all_doc[a[0][0]]
    db = pymysql.connect("localhost", "root", "", "wechat")
    cursor = db.cursor()
    sql = """select content from knowledge where keywords = "%s" """ % (keywords)
    print(sql)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        for row in results:  # 从fetchall中读取操作
            # print(row)
            str1 = str(row)
            # print(str1)
            str2 = str1[2:-3]
            print(str2)
    except:
        # 如果发生错误则回滚
        db.rollback()
    # 关闭数据库连接
    db.close()
